<?php

namespace common\components\office;

use Yii;
use yii\base\Exception;
use yii\helpers\Json;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use common\models\workbook\WorkbookRecord;
use common\models\workbook\WorkbookMember;
use mpend\modules\team\models\WorkBookStatsMemberSearch;
use common\widgets\grid\ExcelGridView;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use common\helpers\FileHelper;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Border;
use common\helpers\ArrayHelper;
use yii\base\InvalidConfigException;
use common\components\office\grid\Column;
use common\components\office\grid\DataColumn;
use PhpOffice\PhpSpreadsheet\IOFactory;

/**
 * Description of newPHPClass
 *
 * @author emhome
 * @since 2.0
 */
class WorkbookExcel extends \yii\base\Component {

    const FORMAT_XLS = 'Xls';
    const FORMAT_XLSX = 'Xlsx';
    const FORMAT_ODS = 'Ods';
    const FORMAT_HTML = 'Csv';
    const FORMAT_CSV = 'Html';
    const FORMAT_TCPDF = 'Tcpdf';
    const FORMAT_DOMPDF = 'Dompdf';
    const FORMAT_MPDF = 'Mpdf';

    /**
     * @var array
     */
    public $data = [];

    /**
     * @var \yii\data\ActiveDataProvider the data provider for the view. This property is required.
     */
    public $dataProvider;

    /**
     * @var array
     */
    public $captions = [];

    /**
     * @var string
     */
    public $title;

    /**
     * @var string
     */
    public $filename;

    /**
     * @var string
     */
    public $savePath;

    /**
     * @var array grid column configuration. Each array element represents the configuration
     * for one particular grid column. For example,
     *
     * ```php
     * [
     *     ['class' => SerialColumn::className()],
     *     [
     *         'class' => DataColumn::className(), // this line is optional
     *         'attribute' => 'name',
     *         'format' => 'text',
     *         'label' => 'Name',
     *     ],
     *     ['class' => CheckboxColumn::className()],
     * ]
     * ```
     *
     * If a column is of class [[DataColumn]], the "class" element can be omitted.
     *
     * As a shortcut format, a string may be used to specify the configuration of a data column
     * which only contains [[DataColumn::attribute|attribute]], [[DataColumn::format|format]],
     * and/or [[DataColumn::label|label]] options: `"attribute:format:label"`.
     * For example, the above "name" column can also be specified as: `"name:text:Name"`.
     * Both "format" and "label" are optional. They will take default values if absent.
     *
     * Using the shortcut format the configuration for columns in simple cases would look like this:
     *
     * ```php
     * [
     *     'id',
     *     'amount:currency:Total Amount',
     *     'created_at:datetime',
     * ]
     * ```
     *
     * When using a [[dataProvider]] with active records, you can also display values from related records,
     * e.g. the `name` attribute of the `author` relation:
     *
     * ```php
     * // shortcut syntax
     * 'author.name',
     * // full syntax
     * [
     *     'attribute' => 'author.name',
     *     // ...
     * ]
     * ```
     */
    public $columns = [];

    /**
     * @var array grid column configuration. Each array element represents the configuration
     */
    public $totalColumns = [];

    /**
     * @var string the default data column class if the class name is not explicitly specified when configuring a data column.
     * Defaults to 'common\components\office\grid\DataColumn'.
     */
    public $dataColumnClass;

    /**
     * @var array|Formatter the formatter used to format model attribute values into displayable texts.
     * This can be either an instance of [[Formatter]] or an configuration array for creating the [[Formatter]]
     * instance. If this property is not set, the "formatter" application component will be used.
     */
    public $formatter;

    /**
     * @var array|Formatter the formatter used to format model attribute values into displayable texts.
     * This can be either an instance of [[Formatter]] or an configuration array for creating the [[Formatter]]
     * instance. If this property is not set, the "formatter" application component will be used.
     */
    public $format = self::FORMAT_XLSX;

    /**
     * @var Spreadsheet
     */
    private $_spreadSheet;

    /**
     * @var integer
     */
    private $_rowIndex = 1;

    /**
     * @var integer
     */
    private $_totalAmount = [];

    /**
     * Initializes the grid view.
     * This method will initialize required property values and instantiate [[columns]] objects.
     */
    public function init() {
        parent::init();
        if ($this->dataProvider === null) {
            throw new InvalidConfigException('The "dataProvider" property must be set.');
        }
        if ($this->formatter === null) {
            $this->formatter = Yii::$app->getFormatter();
        } elseif (is_array($this->formatter)) {
            $this->formatter = Yii::createObject($this->formatter);
        }
        if (!$this->formatter instanceof \yii\i18n\Formatter) {
            throw new InvalidConfigException('The "formatter" property must be either a Format object or a configuration array.');
        }
        $this->createSpreadSheet();
        $this->initColumns();
        if (!empty($this->totalColumns)) {
            $this->_totalAmount = array_combine($this->totalColumns, array_fill(0, count($this->totalColumns), 0));
        }
    }

    /**
     * @return Spreadsheet
     */
    public function getSpreadSheet() {
        return $this->_spreadSheet;
    }

    /**
     * 实例化 Spreadsheet 对象
     */
    public function createSpreadSheet() {
        $this->_spreadSheet = Yii::createObject(Spreadsheet::class);
    }

    /**
     * 实例化 Spreadsheet sheet 对象
     * @return Worksheet
     */
    public function getSheet() {
        $spreadsheet = $this->getSpreadSheet();
        return $spreadsheet->getActiveSheet();
    }

    public function render() {
        $this->renderTitle();
        $this->renderTableCaption();
        $this->renderTableHeader();
        $this->renderTableBody();
        $this->renderTableFooter();
    }

    public function getColspan() {
        return count($this->columns);
    }

    public function renderTitle() {
        if (!$this->title) {
            return;
        }
        $sheet = $this->getSheet();
        $rowIndex = $this->_rowIndex++;
        $row = $sheet->getRowDimension($rowIndex);
        $cell = $sheet->getCellByColumnAndRow(1, $rowIndex);
        $cell->setValue($this->title);
        $params = [
            1,
            $rowIndex,
            $this->colspan,
            $rowIndex
        ];
        $method = array($sheet, 'mergeCellsByColumnAndRow');
        call_user_func_array($method, $params);
        $row->setRowHeight(100);
        $style = call_user_func_array(array($sheet, 'getStyleByColumnAndRow'), $params);
        $style->applyFromArray($this->getTitleOptions());
    }

    public function getTitleOptions() {
        return [
            'font' => [
                'name' => 'Arial',
                'bold' => true,
                'italic' => false,
                'underline' => Font::UNDERLINE_DOUBLE,
                'strikethrough' => false,
                'color' => [
                    'rgb' => '808080'
                ]
            ],
            'borders' => [
                'bottom' => [
                    'borderStyle' => Border::BORDER_DASHDOT,
                    'color' => [
                        'rgb' => '808080'
                    ]
                ],
                'top' => [
                    'borderStyle' => Border::BORDER_DASHDOT,
                    'color' => [
                        'rgb' => '808080'
                    ]
                ]
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ],
            'quotePrefix' => true
        ];
    }

    public function renderTableCaption() {
        if (empty($this->captions)) {
            return;
        }
        $sheet = $this->getSheet();
        foreach ($this->captions as $item) {
            $text = ArrayHelper::getValue($item, 'text');
            $styles = ArrayHelper::getValue($item, 'styles');
            $height = ArrayHelper::getValue($item, 'height');
            $rowIndex = $this->_rowIndex++;
            $row = $sheet->getRowDimension($rowIndex);
            if ($height) {
                $row->setRowHeight(100);
            }
            $cell = $sheet->getCellByColumnAndRow(1, $rowIndex);
            if ($text) {
                $cell->setValue($text);
            }
            $params = [
                1,
                $rowIndex,
                $this->colspan,
                $rowIndex
            ];
            $method = array($sheet, 'mergeCellsByColumnAndRow');
            call_user_func_array($method, $params);
            if (is_array($styles) && !empty($styles)) {
                $cellStyle = call_user_func_array(array($sheet, 'getStyleByColumnAndRow'), $params);
                $cellStyle->applyFromArray($styles);
            }
        }
    }

    public function renderTableHeader() {
        $sheet = $this->getSheet();

        $rowHeight = 50;
        $rowIndex = $this->_rowIndex++;
        $row = $sheet->getRowDimension($rowIndex);
        if ($rowHeight) {
            $row->setRowHeight($rowHeight);
        }
        $colIndex = 1;

        foreach ($this->columns as $column) {
            /* @var $column Column */
            $text = $column->renderHeaderCell();
            $cell = $sheet->getCellByColumnAndRow($colIndex, $rowIndex);
            if ($text) {
                $cell->setValue($text);
            }
            $styles = $column->headerOptions;
            if (is_array($styles) && !empty($styles)) {
                $width = ArrayHelper::remove($styles, 'width');
                if ($width) {
                    $sheet->getColumnDimensionByColumn($colIndex)->setWidth($width);
                }
                $cellStyle = $sheet->getStyleByColumnAndRow($colIndex, $rowIndex);
                $cellStyle->applyFromArray($styles);
            }

            $colIndex++;
        }
    }

    /**
     * Creates column objects and initializes them.
     */
    protected function initColumns() {
        if (empty($this->columns)) {
            $this->guessColumns();
        }
        foreach ($this->columns as $i => $column) {
            if (is_string($column)) {
                $column = $this->createDataColumn($column);
            } else {
                $column = Yii::createObject(array_merge([
                    'class' => $this->dataColumnClass ?: DataColumn::className(),
                    'grid' => $this,
                ], $column));
            }
            if (!$column->visible) {
                unset($this->columns[$i]);
                continue;
            }
            $this->columns[$i] = $column;
        }
    }

    public function renderTableBody() {
        $total = $this->dataProvider->getTotalCount();
        if (!$total && $this->emptyText !== false) {
            return $this->renderEmpty();
        }
        $models = array_values($this->dataProvider->getModels());
        $keys = $this->dataProvider->getKeys();
        foreach ($models as $index => $model) {
            $key = $keys[$index];
            $this->renderTableRow($model, $key, $index);
            $this->_rowIndex++;
        }
    }

    /**
     * Renders a table row with the given data model and key.
     * @param mixed $model the data model to be rendered
     * @param mixed $key the key associated with the data model
     * @param int $index the zero-based index of the data model among the model array returned by [[dataProvider]].
     * @return string the rendering result
     */
    public function renderTableRow($model, $key, $index) {
        $sheet = $this->getSheet();
        $colIndex = 1;
        $useTotal = !empty($this->totalColumns);
        /* @var $column Column */
        foreach ($this->columns as $column) {
            $text = $column->renderDataCell($model, $key, $index);
            if ($useTotal && $column instanceof DataColumn) {
                $_temp = ArrayHelper::getValue($this->_totalAmount, $column->attribute, false);
                if ($_temp !== false) {
                    ArrayHelper::setValue($this->_totalAmount, $column->attribute, (float) $text + $_temp);
                }
            }
            $cell = $sheet->getCellByColumnAndRow($colIndex, $this->_rowIndex);
            $cell->setValue($text);
            $styles = $column->contentOptions;
            if (is_array($styles) && !empty($styles)) {
                $cellStyle = $sheet->getStyleByColumnAndRow($colIndex, $this->_rowIndex);
                $cellStyle->applyFromArray($styles);
            }
            $colIndex++;
        }
    }

    /**
     * Renders the HTML content indicating that the list view has no data.
     * @return string the rendering result
     * @see emptyText
     */
    public function renderEmpty() {
        if ($this->emptyText === false) {
            return '';
        }
        return '';
    }

    public function renderTableFooter() {
        if (empty($this->totalColumns)) {
            return;
        }
        $sheet = $this->getSheet();
        $colIndex = 1;
        /* @var $column DataColumn */
        foreach ($this->columns as $column) {
            if ($column instanceof DataColumn) {
                $text = ArrayHelper::getValue($this->_totalAmount, $column->attribute);
            } else {
                $text = $column->footer;
            }
            $cell = $sheet->getCellByColumnAndRow($colIndex++, $this->_rowIndex);
            $cell->setValue($text);
        }
        $this->_rowIndex++;
    }

    public function getSaveRealPath() {
        $path = Yii::getAlias('@webroot') . $this->getFilePath();
        FileHelper::createDirectory($path);
        return $path;
    }

    public function getFilePath() {
        $filePath = pathinfo($this->savePath, PATHINFO_DIRNAME);
        if (!$filePath) {
            $filePath = '/assets/file/';
        }
        return '/' . trim($filePath, '/') . '/';
    }

    public function getFileName() {
        $extension = mb_strtolower($this->format);
        if (!$this->filename) {
            $this->filename = FileHelper::buildUploadFileName($extension);
        }
        $filename = $this->filename;
        $ext = pathinfo($filename, PATHINFO_EXTENSION);
        if (!$ext) {
            $filename .= '.' . $extension;
        } else {
            $filename = str_replace('.' . $ext, '.' . $extension, basename($filename));
        }
        return $filename;
    }

    public function export() {
        $this->render();
        $this->saveFile($this->saveRealPath . $this->fileName);
        return $this->filePath . $this->fileName;
    }

    public function saveFile($path) {
        $writer = IOFactory::createWriter($this->getSpreadSheet(), $this->format);
        $writer->save($path);
    }

    /**
     * Creates a [[DataColumn]] object based on a string in the format of "attribute:format:label".
     * @param string $text the column specification string
     * @return DataColumn the column instance
     * @throws InvalidConfigException if the column specification is invalid
     */
    protected function createDataColumn($text) {
        if (!preg_match('/^([^:]+)(:(\w*))?(:(.*))?$/', $text, $matches)) {
            throw new InvalidConfigException('The column must be specified in the format of "attribute", "attribute:format" or "attribute:format:label"');
        }

        return Yii::createObject([
            'class' => $this->dataColumnClass ?: DataColumn::className(),
            'grid' => $this,
            'attribute' => $matches[1],
            'format' => isset($matches[3]) ? $matches[3] : 'text',
            'label' => isset($matches[5]) ? $matches[5] : null,
        ]);
    }

    /**
     * This function tries to guess the columns to show from the given data
     * if [[columns]] are not explicitly specified.
     */
    protected function guessColumns() {
        $models = $this->dataProvider->getModels();
        $model = reset($models);
        if (is_array($model) || is_object($model)) {
            foreach ($model as $name => $value) {
                if ($value === null || is_scalar($value) || is_callable([$value, '__toString'])) {
                    $this->columns[] = (string) $name;
                }
            }
        }
    }

}
